package cloud.xlh.bigdatahandle.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import java.util.UUID;

/**
 * Created with IntelliJ IDEA.
 *
 * @ project name : xlh_osms
 * @ Author: XuLeHuang
 * @ Date: 2023/7/15 17:47
 * @ Description:
 */
public class JDBCBatchHandleUtil {

    private static final String url = "";
    private static final String user = "";
    private static final String password = "";


    public static void insertBigData(String sqlStatement) {
        //代码内无事务
        long begin = 33112001;//起始id
        long end = begin+500000;//每次循环插入的数据量
        //定义连接、statement对象
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            //加载jdbc驱动
            //Class.forName("com.mysql.jdbc.Driver");
            Class.forName("com.mysql.jdbc.Driver");
            //连接mysql
            Properties properties = new Properties();
            //InputStream resourceAsStream = EncodeStarterApplication.class.getResourceAsStream("D:\\development\\environment\\idea\\IdeaProjects\\example\\encode-starter\\src\\main\\resources\\key.properties");
           /* FileInputStream fileInputStream = new FileInputStream("D:\\development\\environment\\idea\\IdeaProjects\\example\\batch_insert_to_mysql\\src\\main\\resources\\spring-mybatis\\jdbc.properties");
            properties.load(fileInputStream);
            String url = properties.getProperty("db.url");
            String user = properties.getProperty("db.name");
            String password = properties.getProperty("db.password");*/
            conn = DriverManager.getConnection(url, user, password);
            //将自动提交关闭
            conn.setAutoCommit(false);
            //编写sql
            String sql = "INSERT INTO t_person VALUES (?,?,?,?)";
            //预编译sql
            pstm = conn.prepareStatement(sql);
            //开始总计时
            long bTime1 = System.currentTimeMillis();
            //循环10次，每次五十万数据，一共500万
            for(int i=0;i<100;i++) {

                //开启分段计时，计1W数据耗时
                long bTime = System.currentTimeMillis();
                //开始循环
                while (begin < end) {
                    //赋值
                    pstm.setString(1, begin+"");
                    pstm.setString(2, UUID.randomUUID().toString());
                    pstm.setInt(3, 1);
                    pstm.setString(4, UUID.randomUUID().toString());

                    //添加到同一个批处理中
                    pstm.addBatch();
                    begin++;
                }
                //执行批处理
                pstm.executeBatch();
                //提交事务
                conn.commit();
                //边界值自增50W
                end += 500000;
                //关闭分段计时
                long eTime = System.currentTimeMillis();
                //log.info("成功插入50W条数据耗时：{}秒"  , (eTime-bTime)/1000);
            }
            //关闭总计时
            long eTime1 = System.currentTimeMillis();
            //输出
            //log.info("成功插入500W条数据耗时：{}秒"  , (eTime1-bTime1)/1000);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }
    }
}
